using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuanLyShopCD.PD
{
    class ProcessData
    {
        SqlConnection con;
        public ProcessData()
        {
            con = new SqlConnection();
            con.ConnectionString = @"Data Source=P502-89\SQLEXPRESS;Initial Catalog=QuanLyShopCD;Integrated Security=True";
        }
        public void Open()
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
        }
        public void Close()
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
        public DataTable GetTable(string SQL)
        {
            try
            {
                Open();
                DataTable tb = new DataTable();
                SqlDataAdapter adp = new SqlDataAdapter(SQL, con);
                adp.Fill(tb);
                return tb;
            }
            catch (Exception ex)
            {

            }
            return null;
        }
        public void ExecuteSQL(string SQL)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand(SQL, con);
                cmd.ExecuteNonQuery();
                Close();
            }
            catch (Exception ex)
            {
            }
        }
        public void ExecuteStoredProcedure(String Name, SqlParameter[] pr)
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name;
            cmd.CommandType = CommandType.StoredProcedure;
            if (pr != null)
                cmd.Parameters.AddRange(pr);
            cmd.ExecuteNonQuery();
            Close();
        }
        public DataTable ExecutePROC_TABLE(String Name, SqlParameter[] pr)
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name;
            cmd.CommandType = CommandType.StoredProcedure;
            if (pr != null)
                cmd.Parameters.AddRange(pr);

            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);

            Close();
            return tb;
        }
        public DataTable GetDataTable_proc(params String[] list)
        {
            List<SqlParameter> listpr = new List<SqlParameter>();
            for (int i = 0; i < list.Length - 1; i++)
            {
                String prname = "@var" + i.ToString();
                SqlParameter pr = new SqlParameter(prname, list[i + 1].ToString());
                listpr.Add(pr);
            }
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = list[0].ToString();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = this.con;
                cmd.Parameters.AddRange(listpr.ToArray());
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable tb = new DataTable();
                adp.Fill(tb);
                Close();
                return tb;
            }
            catch (SqlException ex)
            {
               
            }
            return null;
        }

    }
}
